var db = require('../model/db')
// 查看函数
function getAllRecords (table, res) {
  var sql = `select * from ${table} `
  db.dbSql(sql, [], (err, results) => {
    if (err) return res.status(500).json({ code: 500, msg: '查询失败！' });
    res.status(200).json({ code: 200, msg: '查询成功！', data: results });

  })
}
// 删除函数
function deleteRecord (table, res, data, id) {
  const sql = `DELETE FROM ${table}  where ${id}=?`;
  db.dbSql(sql, data, (err, result) => {
    if (err) return res.status(500).json({ code: 500, msg: '删除失败' });
    res.status(200).json({ code: 200, msg: '删除成功' });
  })
}

// 更新函数
function updateById (table, data, id, res) {
  const keys = Object.keys(data);
  const values = Object.values(data);
  const setClause = keys.map((key, index) => `${key} = '${values[index]}'`).join(', ');
  const sql = `UPDATE ${table} SET ${setClause} WHERE ${id}=${data[id]}`;
  console.log(sql);
  db.dbSql(sql, data, (err, results) => {
    if (err) return res.status(500).json({ code: 500, msg: '更新失败' })
    res.json({ code: 200, msg: '更新成功!' })
  })
}
// 分页函数
function page (table, req, res, id) {
  var { pageNum, pageSize } = req.query
  // 参数验证
  if (!pageNum || !pageSize || pageNum <= 0 || pageSize <= 0) {
    return res.status(400).json({ code: 400, msg: '参数错误' })
  }
  var offset = (--pageNum) * pageSize
  var sql = `SELECT * FROM ${table} where ${id} LIMIT ?, ?`
  db.dbSql(sql, [offset, parseInt(pageSize)], function (err, results) {
    if (err) return res.status(500).json({ code: 500, msg: '查询失败' })
    res.json({ code: 200, msg: '查询成功!', data: results })
  })
}
// 新增函数
function dbInsert (table, data, res) {
  const keys = Object.keys(data);
  const values = Object.values(data);
  const placeholders = values.map(() => '?').join(', ');
  const sql = `INSERT INTO ${table} (${keys.join(', ')}) VALUES (${placeholders})`;
  db.dbSql(sql, values, (err, results) => {
    if (err) { console.error(err); return res.status(500).json({ code: 500, msg: '新增失败！' }); }
    res.json({ code: 200, msg: '新增成功！', data: results });
  });
}


// 搜索函数


/**
 * 查询指定表中符合条件的记录
 * @param {string} table - 表名
 * @param {string} field - 查询字段
 * @param {object} res - 响应对象
 * @param {string} content - 查询参数
 */
function search (table, field, res, content) {
  let sql = '';
  if (content == '') {
    sql = `SELECT * FROM ${table}`;
  } else {
    sql = `SELECT * FROM ${table} WHERE ${field} LIKE ?`;
  }
  const params = [`${content}%`];
  db.dbSql(sql, params, (err, results) => {
    if (err) {
      return res.status(500).json({ code: 500, msg: '查询失败！' });
    }
    res.status(200).json({ code: 200, msg: '查询成功！', data: results });
  });
}
module.exports = {
  deleteRecord, getAllRecords, page, updateById, dbInsert, search
}


